﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.Diagnostics;
using System.Linq;



namespace TSQLPARSER
{
    public sealed partial class xParser
    {
        /// <summary>
        /// Hold final List of Results, one xStatement per TSQL statement
        /// </summary>
        private List<xStatement> xStmtlist = new List<xStatement>();

        private Dictionary<int, List<xStatement>> Allstmts = new Dictionary<int, List<xStatement>>();
        /// <summary>
        /// Holds recursive data.
        /// </summary>
        private Stack<xStatement> xQ = new Stack<xStatement>();

        /// <summary>
        /// Current  TSQL statement being Processed
        /// </summary>
        private xStatement _CurrentStatement;
        
        string FileText = "";

        public Dictionary<int, List<xStatement>> GroupedOutput
        {
            get
            {
                return Allstmts;
            }
        }

        /// <summary>
        /// Output of this Class
        /// </summary>
        public List<xStatement> ParsedOutput
        {
            get
            {
                return xStmtlist;
            }
        }

        private int Currentstmt = 0;

        public List<string> NotParsed { get; set; }

        /// <summary>
        /// Main function for parsing TSQL
        /// </summary>
        /// <param name="FileText"></param>
        /// <returns></returns>
        public string Parse(string FileText)
        {
            NotParsed = new List<string>();
            this.FileText=FileText;
            System.IO.StringReader StrInput = new System.IO.StringReader(FileText);

            TSql100Parser _parser = new TSql100Parser(true);
            IList<ParseError> Errors;
            IScriptFragment Fragments = _parser.Parse(StrInput, out Errors);

            if (Errors.Count > 0)
            {
                string Errorout=string.Empty;
                foreach (ParseError pError in Errors)
                {
                    Errorout += pError.Message + "-->" + pError.Line + "\n";
                }

                return Errorout;
            }
            TSqlScript script = Fragments as TSqlScript;
            foreach (TSqlBatch batch in script.Batches)
            {
                foreach (TSqlStatement statement in batch.Statements)
                {
                   
                    xProcessStatements(statement);
                    
                }
            }
            return "";
        }


        private void xProcessStatements(TSqlFragment Stmt)
        {
            List<TSqlFragment> selectStatements = new List<TSqlFragment>();
            FindStatements<TSqlFragment>(Stmt, selectStatements);
            foreach (TSqlFragment subStmt in selectStatements)
            {
                Currentstmt++;
                Allstmts.Add(Currentstmt, new List<xStatement>());

                if (subStmt is SelectStatement)
                {
                    xSelectProcessing(subStmt);
                }
                else if (subStmt is DeleteStatement)
                {
                    xdeleteProcessing(subStmt);
                }
                else if (subStmt is InsertStatement)
                {
                    xInsertProcessing(subStmt);
                }
                else if (subStmt is UpdateStatement)
                {
                    xUpdateProcessing(subStmt);
                }
                else if (subStmt is ViewStatementBody)
                {
                    xViewProcessing(subStmt);
                }

                else if (subStmt is TruncateTableStatement)
                {
                    _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>() };
                    ProcessSchemaObject((subStmt as TruncateTableStatement).TableName);
                    _CurrentStatement.xTableList[0].Tbltype = "TRUNCATETABLE";
                    AddtoList(_CurrentStatement, subStmt);
                }
                else 
                {
                    if (subStmt is DeclareVariableStatement || subStmt is SetVariableStatement || subStmt is UseStatement) continue;
                    _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>() };
                    ProcessTSqlFragment(subStmt);
                    if (_CurrentStatement != null && _CurrentStatement.IsPopulated) AddtoList(_CurrentStatement, subStmt);
                    
                }
            }
        }

        private void xViewProcessing(TSqlFragment subStmt)
        {
            _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>(), Type = "VIEW-SELECT" };
            ViewStatementBody View = subStmt as ViewStatementBody;
            foreach (Identifier c in View.Columns) ProcessTSqlFragment(c);
            //ProcessSchemaObject(View.SchemaObjectName);
            xSelectProcessing(View.SelectStatement);
        }

        /// <summary>
        /// Main function to process delete TSQL statemenets 
        /// </summary>
        /// <param name="subStmt"></param>
        private void xdeleteProcessing(TSqlFragment subStmt)
        {
            _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>(),Type="DELETE" };
            DeleteStatement xd = subStmt as DeleteStatement;
            ProcessTargetTables(xd.Target as SchemaObjectDataModificationTarget);
            SetTableType("DELET-TARGET-TABLE");
            ProcessSourceTables(xd.FromClauses);
            SetTableType("DELETE-FROM-TABLE");
            ProcessTSqlFragment(xd.WhereClause);
            if (_CurrentStatement.IsPopulated)
            {
                AddtoList(_CurrentStatement, subStmt);
            }
            
        }
        /// <summary>
        /// Main process for select Processing
        /// </summary>
        /// <param name="Stmt"></param>
        private void xSelectProcessing(TSqlFragment Stmt)
        {
            if (_CurrentStatement != null && _CurrentStatement.IsPopulated)
            {
                xQ.Push(_CurrentStatement);
                _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>() };
            }
            if (_CurrentStatement == null)
                _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>(), Type = "SELECT" };

            SelectStatement biSelect = Stmt as SelectStatement;
            if (biSelect != null)
            {
                if (biSelect.QueryExpression is BinaryQueryExpression)
                {
                    BinaryQueryExpression bQ = biSelect.QueryExpression as BinaryQueryExpression;
                    CheckBinaryExpressions(bQ);
                }
                else
                {
                    ProcessSelect(biSelect.QueryExpression);
                    ProcessTSqlFragment(biSelect.OrderByClause);
                }
            }

            else
            {
                ProcessSelect(Stmt);
            }

            if (_CurrentStatement.IsPopulated)
            {   
                AddtoList(_CurrentStatement, Stmt);
            }
          
        }

        /// <summary>
        /// Main function for update processing
        /// </summary>
        /// <param name="Stmt"></param>
        private void xUpdateProcessing(TSqlFragment Stmt)
        {
            _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>(), Type = "UPDATE" };
            Processupdates(Stmt as UpdateStatement);
            if (_CurrentStatement.IsPopulated)AddtoList(_CurrentStatement, Stmt);
            
        }

        /// <summary>
        /// Main function for Insert TSQL Processing
        /// </summary>
        /// <param name="Stmt"></param>
        private void xInsertProcessing(TSqlFragment Stmt)
        {
            _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>(),Type= "INSERT" };
            ProcessInserts(Stmt as InsertStatement);

            if (_CurrentStatement.IsPopulated) AddtoList(_CurrentStatement, Stmt);
            
        }


        /// <summary>
        /// Main function for Update TSQL Processing
        /// </summary>
        /// <param name="Updt"></param>
        private void Processupdates(UpdateStatement Updt)
        {
            ProcessTargetTables(Updt.Target as SchemaObjectDataModificationTarget);
            SetTableType("UPDATE-TARGET-TABLE");
            foreach (AssignmentSetClause Sc in Updt.SetClauses)ProcessTSqlFragment(Sc.Column);

            //TableVariables cause crash.
            string Alias="";
            if (_CurrentStatement.xTableList.Count == 0) Alias = "@TableVariable";
            else
                Alias = string.IsNullOrEmpty(_CurrentStatement.xTableList[0].Alias) ? _CurrentStatement.xTableList[0].TableName.ToUpper() : _CurrentStatement.xTableList[0].Alias.ToUpper();

            //to connect columns with the Update Table
            _CurrentStatement.xColumnList.ForEach(delegate(xColumn Column){Column.TableAlias = Alias;});
           
            foreach (AssignmentSetClause Sc in Updt.SetClauses)
            {
                ProcessTSqlFragment(Sc.NewValue);
            }
            ProcessSourceTables(Updt.FromClauses);
            SetTableType("UPDATE-SOURCE-TABLE");
            ProcessTSqlFragment(Updt.WhereClause);
        }

        /// <summary>
        /// Sub process for insert processing
        /// </summary>
        /// <param name="Stmt"></param>
        private void ProcessInserts(InsertStatement Stmt)
        {
            foreach (Column c in Stmt.Columns) GetColumns(c);
            ProcessTargetTables(Stmt.Target as SchemaObjectDataModificationTarget);
            SetTableType("INSERT-TARGET-TABLE");
            if (Stmt.InsertSource is SelectStatement)
            if ((Stmt.InsertSource as SelectStatement).QueryExpression is BinaryQueryExpression)
            {
                BinaryQueryExpression bQ = (Stmt.InsertSource as SelectStatement).QueryExpression as BinaryQueryExpression;
                CheckBinaryExpressions(bQ);
            }
            else
            {
                ProcessSelect((Stmt.InsertSource as SelectStatement).QueryExpression);
            }
            SetTableType("INSERT-SOURCE-TABLE");
        }


        /// <summary>
        /// Handles Multiple UNIONS
        /// </summary>
        /// <param name="biSQL"></param>
        private void CheckBinaryExpressions(TSqlFragment biSQL)
        {
            BinaryQueryExpression bSQL = biSQL as BinaryQueryExpression;
            if (bSQL == null) return;
            
            if (bSQL.FirstQueryExpression is BinaryQueryExpression) CheckBinaryExpressions(bSQL.FirstQueryExpression);
            else
                ProcessSelect(bSQL.FirstQueryExpression);
            if (bSQL.SecondQueryExpression is BinaryQueryExpression) CheckBinaryExpressions(bSQL.SecondQueryExpression);
            else
                ProcessSelect(bSQL.SecondQueryExpression);
        }

        private void ProcessSelect(TSqlFragment Select)
        {
            if (_CurrentStatement != null && _CurrentStatement.IsPopulated)
            {
                xQ.Push(_CurrentStatement);
                _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>() };
            }
            if (_CurrentStatement==null)
                _CurrentStatement = new xStatement() { xColumnList = new List<xColumn>(), xTableList = new List<xTable>() };


            CheckBinaryExpressions(Select);


            QuerySpecification Qs = Select as QuerySpecification;
            if (Qs != null)
            {
                foreach (TSqlFragment I in Qs.SelectElements)
                {
                    ProcessTSqlFragment(I);
                }

                ProcessSchemaObject(Qs.Into);
                SetTableType("SELECT-INTO-TABLE");
                ProcessSourceTables(Qs.FromClauses);
                SetTableType("SELECT-FROM-TABLE");
                ProcessTSqlFragment(Qs.WhereClause);
                ProcessTSqlFragment(Qs.GroupByClause);
                ProcessTSqlFragment(Qs.HavingClause);

            }
            // this is to handle select within selects \ when within Recursive loops .
            if (xQ.Count > 0)
            {  
                if (_CurrentStatement.IsPopulated)
                {
                    AddtoList(_CurrentStatement, Select);
                }
                _CurrentStatement = xQ.Pop();
            }

        }

        private void ProcessSchemaObject(SchemaObjectName schemaObjectName)
        {
            if (schemaObjectName != null)
            {
                _CurrentStatement.xTableList.Add(new xTable()
                {
                    Alias = "",
                    Database = schemaObjectName.DatabaseIdentifier == null ? "" : schemaObjectName.DatabaseIdentifier.Value,
                    Server = schemaObjectName.ServerIdentifier == null ? "" : schemaObjectName.ServerIdentifier.Value,
                    TableName = schemaObjectName.BaseIdentifier == null ? "" : schemaObjectName.BaseIdentifier.Value
                });

                
            }
        }
       
        private void ProcessTargetTables(SchemaObjectDataModificationTarget ts)
        {
            if (ts == null) return;
            _CurrentStatement.xTableList.Add(new xTable()
            {
                
                Database = ts.SchemaObject.DatabaseIdentifier == null ? "" : ts.SchemaObject.DatabaseIdentifier.Value,
                Server = ts.SchemaObject.ServerIdentifier == null ? "" : ts.SchemaObject.ServerIdentifier.Value,
                TableName = ts.SchemaObject.BaseIdentifier == null ? "" : ts.SchemaObject.BaseIdentifier.Value,
                Alias = _CurrentStatement.Type == "INSERT" ? "INSERT" : ts.SchemaObject.BaseIdentifier == null ? "" : ts.SchemaObject.BaseIdentifier.Value.ToUpper() 
            });

            if(_CurrentStatement.Type=="INSERT")
             // to link insert table with insert columns
            _CurrentStatement.xColumnList.ForEach(delegate(xColumn Column)
            {
                Column.TableAlias = "INSERT";
            }
           );

            ts.Parameters.ToList().ForEach(delegate(Expression E)
            {
                ProcessTSqlFragment(E);
            }
            );
        }

        private void ProcessSourceTables(IList<TableSource> Tbls)
        {

            foreach (TSqlFragment tbl in Tbls)
            {
               
                switch (tbl.GetType().Name)
                {

                    case "SchemaObjectTableSource":
                        {
                            SchemaObjectTableSource ts = tbl as SchemaObjectTableSource;
                            _CurrentStatement.xTableList.Add(new xTable()
                            {
                                Alias = ts.Alias == null ? "" : ts.Alias.Value.ToUpper(),
                                Database = ts.SchemaObject.DatabaseIdentifier == null ? "" : ts.SchemaObject.DatabaseIdentifier.Value,
                                Server = ts.SchemaObject.ServerIdentifier == null ? "" : ts.SchemaObject.ServerIdentifier.Value,
                                TableName = ts.SchemaObject.BaseIdentifier == null ? "" : ts.SchemaObject.BaseIdentifier.Value
                            });
                            foreach (Expression E in ts.Parameters)
                            {
                                ProcessTSqlFragment(E);
                            }
                            break;
                        }
                
                    case "UnqualifiedJoin":
                        {
                            UnqualifiedJoin Jn = tbl as UnqualifiedJoin;
                            ProcessSourceTables(new List<TableSource> { Jn.FirstTableSource });
                            ProcessSourceTables(new List<TableSource> { Jn.SecondTableSource });
                            break;
                        }
                    case "JoinParenthesis":
                        {
                            JoinParenthesis Jn = tbl as JoinParenthesis;
                            ProcessSourceTables(new List<TableSource> { Jn.Join });
                            break;
                        }
                    case "QualifiedJoin":
                        {
                            QualifiedJoin Jn = tbl as QualifiedJoin;
                            ProcessTSqlFragment(Jn.SearchCondition);
                            ProcessSourceTables(new List<TableSource> { Jn.FirstTableSource });
                            ProcessSourceTables(new List<TableSource> { Jn.SecondTableSource });
                            break;
                        }
                    case "QueryDerivedTable":
                        {
                            //Handles derived tables with ALIAS
                            TableSourceWithAlias QtblAlias = tbl as TableSourceWithAlias;
                            if (QtblAlias.Alias != null)
                                _CurrentStatement.xTableList.Add(new xTable()
                                {
                                    Alias = QtblAlias.Alias == null ? "" : QtblAlias.Alias.Value.ToUpper(),
                                    TableName = QtblAlias.Alias.Value.ToUpper()
                                });

                            QueryDerivedTable Qtbl = tbl as QueryDerivedTable;
                            ProcessSelect(Qtbl.Subquery.QueryExpression);
                            break;
                        }
                    default:
                        NotParsed.Add(tbl.GetType().Name + ">>"+GetSQLSnippet(tbl.StartOffset, tbl.FragmentLength));
                        
                        break;
                }
            }

        }


        /// <summary>
        /// For getting data from various types of statements, called recursively 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="v"></param>
        private void ProcessTSqlFragment<T>(T v) where T : TSqlFragment
        {
            if (v == null) return;
            switch (v.GetType().Name)
            {
                case "Column":
                    {
                        GetColumns(v as Column);
                        break;
                    }
                case "FunctionCall":
                    {
                        ProcessFunction(v as FunctionCall);
                        break;
                    }
                case "RightFunctionCall":
                    {
                        foreach (Expression E in (v as RightFunctionCall).Parameters)
                        {
                            ProcessTSqlFragment(E);
                        }
                        break;
                    }
                case "LeftFunctionCall":
                    {
                        foreach (Expression E in (v as LeftFunctionCall).Parameters)
                        {
                            ProcessTSqlFragment(E);
                        }
                        break;
                    }
                case "ConvertCall":
                    {
                        ProcessCovert((v as ConvertCall));
                        break;
                    }
                case "OrderByClause":
                    {
                        IList<ExpressionWithSortOrder> Orderbys = (v as OrderByClause).OrderByElements;
                        Orderbys.ToList().ForEach(delegate(ExpressionWithSortOrder Exp)
                        {
                            ProcessTSqlFragment(Exp.Expression);
                        }
                        );
                        break;
                    }
                case "CastCall":
                    {
                        ProcessCast((v as CastCall));
                        break;
                    }
                case "SelectSetVariable":
                    {
                        ProcessTSqlFragment((v as SelectSetVariable).Expression);
                        break;
                    }
                case "SelectColumn":
                    {
                        SelectColumn xv = v as SelectColumn;
                        ProcessTSqlFragment(xv.Expression);
                        ProcessTSqlFragment(xv.ColumnName);
                        break;
                    }
                case "Identifier":
                    {
                        GetIndetifiers((v as Identifier));
                        break;
                    }
                case "BinaryExpression":
                    {
                        BinaryExpression xv = v as BinaryExpression;
                        ProcessTSqlFragment(xv.FirstExpression);
                        ProcessTSqlFragment(xv.SecondExpression);
                        break;
                    }
                case "TernaryExpression":
                    {
                        TernaryExpression xv = v as TernaryExpression;
                        ProcessTSqlFragment(xv.FirstExpression);
                        ProcessTSqlFragment(xv.SecondExpression);
                        ProcessTSqlFragment(xv.ThirdExpression);
                        break;
                    }
                case "LikePredicate":
                    {
                        LikePredicate xv = v as LikePredicate;
                        ProcessTSqlFragment(xv.FirstExpression);
                        ProcessTSqlFragment(xv.SecondExpression);
                        break;
                    }

                case "UnaryExpression":
                    {
                        ProcessTSqlFragment((v as UnaryExpression).Expression);
                        break;
                    }
                case "ExistsPredicate":
                    {
                        ProcessTSqlFragment((v as ExistsPredicate).Subquery);
                        break;
                    }

                case "InPredicate":
                    {
                        InPredicate xv = v as InPredicate;
                        ProcessTSqlFragment(xv.Subquery);
                        ProcessTSqlFragment(xv.Expression);
                        break;
                    }
                case "ParenthesisExpression":
                    {
                        ProcessTSqlFragment((v as ParenthesisExpression).Expression);
                        break;
                    }
                case "GroupByClause":
                    {
                        foreach (ExpressionGroupingSpecification Gs in (v as GroupByClause).GroupingSpecifications)
                        {
                            ProcessTSqlFragment(Gs.Expression);
                        }
                        break;
                    }
                case "HavingClause":
                    {
                        ProcessTSqlFragment((v as HavingClause).SearchCondition);
                        break;
                    }
                case "WhereClause":
                    {
                        ProcessTSqlFragment((v as WhereClause).SearchCondition);
                        break;
                    }
                case "SelectStatement":
                    {
                        xSelectProcessing((v as SelectStatement).QueryExpression);
                        break;
                    }
                case "Subquery":
                    {
                        ProcessSelect((v as Subquery).QueryExpression);
                        break;
                        
                    }
                case "CaseExpression":
                    {
                        CaseExpression xCase = v as CaseExpression;
                        ProcessTSqlFragment(xCase.ElseExpression);
                        ProcessTSqlFragment(xCase.InputExpression);
                        foreach (WhenClause whn in xCase.WhenClauses)
                        {
                            ProcessTSqlFragment(whn.ThenExpression);
                            ProcessTSqlFragment(whn.WhenExpression);
                        }
                        break;
                    }
                        
                
                default:

                    NotParsed.Add(v.GetType().Name + " >> "+GetSQLSnippet(v.StartOffset, v.FragmentLength));
                    
                    break;
            }

        }
       
        /// <summary>
        /// gets the statements with a given statement. 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statement"></param>
        /// <param name="statementList"></param>
        private void FindStatements<T>(TSqlFragment statement, List<T> statementList) where T : TSqlFragment
        {
            if (statement == null) return;
            switch (statement.GetType().Name)
            {
                case "IfStatement":
                    {
                        IfStatement ifStatement = statement as IfStatement;
                        FindStatements<T>((ifStatement.ThenStatement) as TSqlStatement, statementList);
                        FindStatements<T>((ifStatement.ElseStatement) as TSqlStatement, statementList);
                        ExistsPredicate Ep = ifStatement.Predicate as ExistsPredicate;
                        if (Ep != null) FindStatements<T>(Ep.Subquery as TSqlFragment, statementList);
                        break;
                    }
                case "WhileStatement":
                    {
                        WhileStatement whileStatement = statement as WhileStatement;
                        FindStatements<T>((whileStatement.Statement) as TSqlStatement, statementList);
                        break;
                    }
                case "BeginEndBlockStatement":
                    {
                        BeginEndBlockStatement stBlock = statement as BeginEndBlockStatement;
                        foreach (TSqlStatement s in stBlock.StatementList.Statements)
                        {
                            FindStatements<T>(s, statementList);
                        }
                        break;
                    }
                case "TryCatchStatement":
                    {
                        TryCatchStatement tryCatchStatement = statement as TryCatchStatement;
                        foreach (TSqlStatement s in tryCatchStatement.TryStatements.Statements)
                        {
                            FindStatements<T>(s, statementList);
                        }
                        foreach (TSqlStatement s in tryCatchStatement.CatchStatements.Statements)
                        {
                            FindStatements<T>(s, statementList);
                        }
                        break;
                    }
                case "ProcedureStatementBodyBase":
                case "CreateProcedureStatement":
                case "AlterProcedureStatement":
                    {
                        
                        ProcedureStatementBodyBase Proc = statement as ProcedureStatementBodyBase;
                        //if (Proc.StatementList == null) break;
                        foreach (TSqlStatement s in Proc.StatementList.Statements)
                        {
                            FindStatements<T>(s, statementList);
                        }
                        
                        break;
                    }
                case "DeclareCursorStatement":
                    {
                        DeclareCursorStatement xCase = statement as DeclareCursorStatement;
                        ProcessTSqlFragment(xCase.CursorDefinition.Select);

                        break;
                    }
                       
                default:
                    if ((T)statement != null)
                        statementList.Add((T)statement);
                    
                    break;
            }
            
          
        }

    }


}
